package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Store;

public class StoreTongService {

	public Record countStore() {
		return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,E.xjnumber FROM"
				+ " (select  count(T.id) as tolnumber from t_dict_store T inner join t_bus_seller AA on T.sellerId=AA.sellerId inner join  t_auth_admin_seller BB on AA.userid=BB.id  )A,(Select count(T.id) as sjshnumber from t_dict_store T inner join t_bus_seller AA on T.sellerId=AA.sellerId inner join  t_auth_admin_seller BB on AA.userid=BB.id where T.state=1)B,"
				+ " (SELECT count(T.id) as sjnumber from t_dict_store T inner join t_bus_seller AA on T.sellerId=AA.sellerId inner join  t_auth_admin_seller BB on AA.userid=BB.id where T.state=2)C,(select count(T.id) as xjnumber from t_dict_store T inner join t_bus_seller AA on T.sellerId=AA.sellerId inner join  t_auth_admin_seller BB on AA.userid=BB.id where T.state=3)E");
	}

	public Page<Record> findByPage(int pageNum, int pageSize,String storeIds, String sellerId, String sStoreId,String beginDate, String endDate,int state) {
		StringBuffer sBuffer = new StringBuffer(" FROM t_dict_store T "
				+ "inner join t_bus_seller A on T.sellerId=A.sellerId "
				+ "inner join  t_auth_admin_seller BB on A.userid=BB.id "
				+ "LEFT JOIN (SELECT TB.storeid, count(TA.id) AS countGoods FROM t_bus_goods TA INNER JOIN t_bus_goods_store_rel TB on TA.good_store=TB.goodstore GROUP BY TB.storeid) B on T.storeid=B.storeid "
				+ "LEFT JOIN (select ts.storeid ,sum(tbol.amount) as countMoney,sum(tbol.count)as countNum from t_bus_order_list tbol "
				+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
				+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
				+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
				+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2  GROUP BY ts.storeid ) C on T.storeid=C.storeid "
				/*+ "LEFT JOIN (select ts.storeid ,sum(tbog.amount) as countGbMoney,sum(tbog.countnum)as countGbNum from t_bus_order_gb tbog "
				+ "left join t_bus_goods tbg on tbg.id = tbog.goodsid  "
				+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
				+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbog.payState=2  GROUP BY ts.storeid ) D on T.storeid=D.storeid "*/
				+ "WHERE 1=1 ");
		System.out.println("sssssssssss"+sBuffer);
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
			sBuffer.append(" AND T.storeid in ('"+sStoreId+"')");
		}
		if(!StrKit.isBlank(storeIds) && !"0".equals(storeIds)){
			sBuffer.append(" AND T.storeid=?");
			params.add(storeIds);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d') BETWEEN ? AND ? ");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notNull(state)&&state!=0){
			sBuffer.append(" AND T.state=?");
			params.add(state);
		}
		sBuffer.append(" ORDER BY T.starttime DESC");
		
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.storeid,T.title,T.scope,A.sellerName,T.starttime,T.aname,T.aphone,T.checktime,T.qstate,T.state,ifnull(B.countGoods,0) countGoods,ifnull(C.countMoney,0) countMoney,ifnull(C.countNum,0) countNum", sBuffer.toString(), params.toArray());
	}
    /**
     * 排序
     * @param pageNum
     * @param pageSize
     * @param storeIds
     * @param sellerId
     * @param sStoreId
     * @param beginDate
     * @param endDate
     * @param date
     * @param state
     * @return
     */
	public Page<Record> findByPage1(int pageNum, int pageSize,String index,String storeIds, String sellerId, String sStoreId,String beginDate, String endDate,int state) {
		StringBuffer sBuffer = new StringBuffer(" FROM t_dict_store T "
				+ "inner join t_bus_seller A on T.sellerId=A.sellerId "
				+ "inner join  t_auth_admin_seller BB on A.userid=BB.id "
				+ "LEFT JOIN (SELECT TB.storeid, count(TA.id) AS countGoods FROM t_bus_goods TA INNER JOIN t_bus_goods_store_rel TB on TA.good_store=TB.goodstore GROUP BY TB.storeid) B on T.storeid=B.storeid "
				+ "LEFT JOIN (select ts.storeid ,sum(tbol.amount) as countMoney,sum(tbol.count)as countNum from t_bus_order_list tbol "
				+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
				+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
				+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
				+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2  GROUP BY ts.storeid ) C on T.storeid=C.storeid "
				+ "WHERE 1=1 and T.state =2");
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
			sBuffer.append(" AND T.storeid in ('"+sStoreId+"')");
		}
		if(!StrKit.isBlank(storeIds) && !"0".equals(storeIds)){
			sBuffer.append(" AND T.storeid=?");
			params.add(storeIds);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d') BETWEEN ? AND ? ");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notNull(state)&&state!=0){
			sBuffer.append(" AND T.state=?");
			params.add(state);
		}
		//订单量”和“销售额”排序，可由下到大／由大到小排序
		if(!StrKit.isBlank(index)){
			switch (index) {
			case "1":
				sBuffer.append(" ORDER BY C.countNum DESC");
				break;
			case "2":
				sBuffer.append(" ORDER BY C.countNum ASC");
				break;
			case "3":
				sBuffer.append(" ORDER BY C.countMoney DESC");
				break;	
			case "4":
				sBuffer.append(" ORDER BY C.countMoney ASC");
				break;		
			}	
		}	
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.storeid,T.title,T.scope,A.sellerName,T.starttime,T.aname,T.aphone,T.checktime,T.qstate,T.state,ifnull(B.countGoods,0) countGoods,ifnull(C.countMoney,0) countMoney,ifnull(C.countNum,0) countNum", sBuffer.toString(), params.toArray());
	}
	public Page<Record> findXSByPage(int pageNum, int pageSize, String storeIds, String sellerId, String sStoreId,String beginDate, String endDate,int date,int state) {
		StringBuffer sBuffer = new StringBuffer(" FROM t_dict_store T inner join t_bus_seller A on T.sellerId=A.sellerId inner join  t_auth_admin_seller BB on A.userid=BB.id LEFT JOIN "
				+ "(SELECT TB.storeid, count(TA.id) AS countGoods FROM t_bus_goods TA INNER JOIN t_bus_goods_store_rel TB on TA.good_store=TB.goodstore GROUP BY TB.storeid) B on T.storeid=B.storeid ");
		switch (date) {
		case 1://当日
			sBuffer.append( "LEFT JOIN (select ts.storeid ,sum(tbol.amount) as countMoney,sum(tbol.count)as countNum from t_bus_order_list tbol "
					+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
					+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid "
					+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
					+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid  where orderState NOT in(1,5,7,9)  and to_days(payDate) =to_days(now()) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		case 2://本周
			sBuffer.append( "LEFT JOIN (select ts.storeid ,sum(tbol.amount) as countMoney,sum(tbol.count)as countNum from t_bus_order_list tbol "
					+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
					+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid "
					+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
					+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where orderState NOT in(1,5,7,9)  and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		case 3://本月
			sBuffer.append( "LEFT JOIN (select ts.storeid ,sum(tbol.amount) as countMoney,sum(tbol.count)as countNum from t_bus_order_list tbol "
				+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
				+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid "
				+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
				+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid  where orderState NOT in(1,5,7,9)  and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		default:
			//默认全部
			sBuffer.append("LEFT JOIN (select ts.storeid ,sum(tbol.amount) as countMoney,sum(tbol.count)as countNum from t_bus_order_list tbol "
				+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
				+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
				+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
				+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid  where orderState NOT in(1,5,7,9)  GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		}
		
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
			sBuffer.append(" AND T.storeid in ('"+sStoreId+"')");
		}
		if(!StrKit.isBlank(storeIds) && !"0".equals(storeIds)){
			sBuffer.append(" AND T.storeid=?");
			params.add(storeIds);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.starttime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notNull(state)&&state!=0){
			sBuffer.append(" AND T.state=?");
			params.add(state);
		}
		sBuffer.append(" ORDER BY countMoney desc,T.starttime DESC");
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.storeid,T.title,T.scope,A.sellerName,T.starttime,T.aname,T.aphone,T.checktime,T.qstate,T.state,ifnull(B.countGoods,0) countGoods,ifnull(C.countMoney,0) countMoney,ifnull(C.countNum,0) countNum", sBuffer.toString(), params.toArray());
	}

	public Record findById(String storeid) {
		return Db.findFirst("select storeid,title,aname,aphone,qstate from t_dict_store where storeid=?",storeid);
	}

	public Store findByStoreId(String storeid) {
		return Store.me().findFirst("select * from t_dict_store where storeid=?",storeid);
	}
	public Store findPageByStoreid(String storeid) {
		return Store.me().findFirst("SELECT T.id,T.storeid,T.title,T.scope,A.sellerName,T.starttime,T.aname,T.aphone,T.checktime,T.qstate,T.state,ifnull(B.countGoods,0) countGoods,ifnull(C.countMoney,0) countMoney,ifnull(C.countNum,0) countNum "
				+ " FROM t_dict_store T inner join t_bus_seller A on T.sellerId=A.sellerId LEFT JOIN "
				+ "(SELECT TB.storeid, count(TA.id) AS countGoods FROM t_bus_goods TA INNER JOIN t_bus_goods_store_rel TB on TA.good_store=TB.goodstore GROUP BY TB.storeid) B on T.storeid=B.storeid"
				+ " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  GROUP BY storeid) C on T.storeid=C.storeid WHERE T.storeid=?",storeid);
	}
	public boolean edit(Store store) {
		return store.update();
	}
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "分公司统计列表", titles, listz);
	}

	public List<Record> findExcelByPage(String storeIds, String sellerId, String sStoreId,String beginDate, String endDate,int date,int state) {
		StringBuffer sBuffer = new StringBuffer("SELECT T.storeid,T.title,T.scope,A.sellerName,T.starttime,T.aname,T.aphone,T.checktime,T.qstate,T.state,ifnull(B.countGoods,0) countGoods,ifnull(C.countMoney,0) countMoney,ifnull(C.countNum,0) countNum FROM t_dict_store T inner join t_bus_seller A on T.sellerId=A.sellerId inner join  t_auth_admin_seller BB on A.userid=BB.id LEFT JOIN "
				+ "(SELECT TB.storeid, count(TA.id) AS countGoods FROM t_bus_goods TA INNER JOIN t_bus_goods_store_rel TB on TA.good_store=TB.goodstore GROUP BY TB.storeid) B on T.storeid=B.storeid");
		switch (date) {
		case 1://当日
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9) and to_days(payDate) =to_days(now()) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		case 2://本周
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  and YEARWEEK(date_format(payDate,'%Y-%m-%d'))= YEARWEEK(now()) GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		case 3://本月
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  and date_format(payDate,'%Y-%m')=date_format(now(),'%Y-%m') GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		default:
			//默认全部
			sBuffer.append( " LEFT JOIN (select storeid,SUM(amount)as countMoney ,SUM(countnum)as countNum from t_bus_order where orderState NOT in(1,5,7,9)  GROUP BY storeid) C on T.storeid=C.storeid WHERE 1=1");
			break;
		}
		
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(sStoreId)&&!"0".equals(sStoreId)){
			sBuffer.append(" AND T.storeid in ('"+sStoreId+"')");
		}
		if(!StrKit.isBlank(storeIds) && !"0".equals(storeIds)){
			sBuffer.append(" AND T.storeid=?");
			params.add(storeIds);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.starttime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notNull(state)&&state!=0){
			sBuffer.append(" AND T.state=?");
			params.add(state);
		}
		sBuffer.append(" ORDER BY countMoney desc,T.starttime DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}

	
}
